#!/usr/bin/env python3
"""
通用查询功能演示脚本
展示如何使用通用查询功能进行数据库操作
"""

import sys
import os
from typing import List, Union
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Boolean
from datetime import datetime

# 添加项目根目录到Python路径
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from src.config.database import Base
from src.services.database_metadata_service import DatabaseMetadataService
from src.services.generic_query_service import GenericQueryService
from src.models.generic_filter import GenericFilter, Condition, Operator, SortOrder, SortField, FilterGroup

# 创建演示用的数据库表
class DemoUser(Base):
    __tablename__ = 'demo_users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(100), unique=True, nullable=False)
    age = Column(Integer)
    department = Column(String(50))
    salary = Column(Integer)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)

def setup_demo_database():
    """设置演示数据库"""
    # 使用SQLite内存数据库
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    
    # 插入演示数据
    with Session(engine) as session:
        users = [
            DemoUser(name='张三', email='zhangsan@example.com', age=25, department='技术部', salary=8000, is_active=True),
            DemoUser(name='李四', email='lisi@example.com', age=30, department='销售部', salary=12000, is_active=True),
            DemoUser(name='王五', email='wangwu@example.com', age=35, department='技术部', salary=15000, is_active=False),
            DemoUser(name='赵六', email='zhaoliu@example.com', age=28, department='市场部', salary=9000, is_active=True),
            DemoUser(name='钱七', email='qianqi@example.com', age=32, department='技术部', salary=13000, is_active=True),
            DemoUser(name='孙八', email='sunba@example.com', age=29, department='销售部', salary=11000, is_active=True),
            DemoUser(name='周九', email='zhoujiu@example.com', age=40, department='管理部', salary=20000, is_active=True),
            DemoUser(name='吴十', email='wushi@example.com', age=26, department='市场部', salary=8500, is_active=False),
        ]
        session.add_all(users)
        session.commit()
    
    return engine

def demo_database_metadata():
    """演示数据库元数据功能"""
    print("🔍 演示数据库元数据功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 获取所有表元数据
        metadata = DatabaseMetadataService.get_all_tables(session)
        print(f"📊 数据库中有 {len(metadata)} 个表")
        
        # 获取特定表元数据
        table_metadata = DatabaseMetadataService.get_table_info(session, 'demo_users')
        if table_metadata:
            print(f"📋 表 'demo_users' 结构:")
            print(f"   主键: {table_metadata.primary_keys}")
            print(f"   列信息:")
            for col in table_metadata.columns:
                print(f"     - {col.name}: {col.type} (nullable: {col.nullable})")
        
        print()

def demo_simple_query():
    """演示简单查询功能"""
    print("🔍 演示简单查询功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 查询所有数据
        filter_obj = GenericFilter(
            conditions=[],
            search=None,
            search_fields=[],
            sort_by=[],
            page=1,
            page_size=10,
            include_total=True
        )
        result = GenericQueryService.query_table(session, 'demo_users', filter_obj)
        print(f"📋 查询所有用户: {len(result.data)} 条记录")
        print(f"   总记录数: {result.total}")
        print()

def demo_condition_query():
    """演示条件查询功能"""
    print("🔍 演示条件查询功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 条件查询：年龄大于30的用户
        conditions: List[Union[Condition, FilterGroup]] = [
            Condition(field='age', operator=Operator.GREATER_THAN, value=30)
        ]
        filter_obj = GenericFilter(
            conditions=conditions,
            search=None,
            search_fields=[],
            sort_by=[],
            page=1,
            page_size=10,
            include_total=True
        )
        result = GenericQueryService.query_table(session, 'demo_users', filter_obj)
        print(f"📋 年龄大于30的用户: {len(result.data)} 条记录")
        for user in result.data:
            print(f"   - {user['name']} ({user['age']}岁)")
        print()

def demo_complex_query():
    """演示复杂查询功能"""
    print("🔍 演示复杂查询功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 复杂查询：技术部且工资大于10000的用户
        conditions: List[Union[Condition, FilterGroup]] = [
            Condition(field='department', operator=Operator.EQUALS, value='技术部'),
            Condition(field='salary', operator=Operator.GREATER_THAN, value=10000)
        ]
        filter_obj = GenericFilter(
            conditions=conditions,
            search=None,
            search_fields=[],
            sort_by=[],
            page=1,
            page_size=10,
            include_total=True
        )
        result = GenericQueryService.query_table(session, 'demo_users', filter_obj)
        print(f"📋 技术部且工资大于10000的用户: {len(result.data)} 条记录")
        for user in result.data:
            print(f"   - {user['name']} ({user['department']}, 工资: {user['salary']})")
        print()

def demo_search_query():
    """演示搜索查询功能"""
    print("🔍 演示搜索查询功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 搜索查询：名字包含"张"的用户
        filter_obj = GenericFilter(
            conditions=[],
            search='张',
            search_fields=['name'],
            sort_by=[],
            page=1,
            page_size=10,
            include_total=True
        )
        result = GenericQueryService.query_table(session, 'demo_users', filter_obj)
        print(f"📋 名字包含'张'的用户: {len(result.data)} 条记录")
        for user in result.data:
            print(f"   - {user['name']}")
        print()

def demo_sort_query():
    """演示排序查询功能"""
    print("🔍 演示排序查询功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 排序查询：按工资降序排列
        sort_by = [SortField(field='salary', order=SortOrder.DESC)]
        filter_obj = GenericFilter(
            conditions=[],
            search=None,
            search_fields=[],
            sort_by=sort_by,
            page=1,
            page_size=5,
            include_total=False
        )
        result = GenericQueryService.query_table(session, 'demo_users', filter_obj)
        print("📋 按工资降序排列的前5名用户:")
        for i, user in enumerate(result.data, 1):
            print(f"   {i}. {user['name']} - 工资: {user['salary']}")
        print()

def demo_pagination():
    """演示分页功能"""
    print("🔍 演示分页功能")
    print("=" * 50)
    
    engine = setup_demo_database()
    
    with Session(engine) as session:
        # 分页查询：第1页，每页3条
        filter_obj = GenericFilter(
            conditions=[],
            search=None,
            search_fields=[],
            sort_by=[],
            page=1,
            page_size=3,
            include_total=True
        )
        result = GenericQueryService.query_table(session, 'demo_users', filter_obj)
        print(f"📋 第1页 (每页3条): {len(result.data)} 条记录")
        print(f"   总记录数: {result.total}")
        print(f"   总页数: {result.total_pages}")
        for user in result.data:
            print(f"   - {user['name']}")
        print()

def main():
    """主演示函数"""
    print("🚀 开始通用查询功能演示")
    print("=" * 60)
    
    # 运行各个演示
    demo_database_metadata()
    demo_simple_query()
    demo_condition_query()
    demo_complex_query()
    demo_search_query()
    demo_sort_query()
    demo_pagination()
    
    print("=" * 60)
    print("🎉 演示完成！")
    print()
    print("📖 功能总结:")
    print("✅ 自动获取数据库表结构")
    print("✅ 支持多种条件查询操作符")
    print("✅ 支持复杂的逻辑组合查询")
    print("✅ 支持全文搜索功能")
    print("✅ 支持多字段排序")
    print("✅ 支持分页和总数统计")
    print("✅ 支持通用的过滤器模式设计")

if __name__ == "__main__":
    main()